package com.hdoit.ares.logical.brokerage.dao.impl;

import java.util.List;

import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import com.hdoit.ares.logical.brokerage.dao.IBrokerageRecordDao;
import com.hdoit.ares.logical.brokerage.model.BrokerageRecord;
import com.hdoit.ares.logical.brokerage.model.ShareBrokerage;
import com.hdoit.ares.logical.cust.model.Customer;
import com.hdoit.ares.logical.viewmodel.brokerage.BrokerageRecordSearch;
import com.hdoit.ares.logical.viewmodel.cust.CustomerSearch;
import com.tennetcn.common.dao.base.IFinderExpression;
import com.tennetcn.common.dao.base.impl.SuperDao;
import com.tennetcn.common.enums.OrderEnum;
import com.tennetcn.common.message.DaoBaseException;
import com.tennetcn.common.message.PagerModel;
import com.tennetcn.common.utils.FinderExpressionFactory;
/**
 * @author      yujinhui
 * @email       
 * @createtime  2017年12月02日 14:26:53
 * @comment
 */
@Component
public class BrokerageRecordDaoImpl extends SuperDao<BrokerageRecord> implements IBrokerageRecordDao{

	@Override
	public List<BrokerageRecord> queryList(BrokerageRecordSearch search, PagerModel pagerModel) {
        IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("brokerageRecord.*","customer.username as customerName","customer.userIdentity as userIdentity")
		                .from(BrokerageRecord.class,"brokerageRecord")
		                .leftJoin(Customer.class, "customer")
		                .on("customer.id","brokerageRecord.userId")
		                .addOrder("brokerageRecord.recordDate", OrderEnum.desc)
		                .setMainTableAlias("brokerageRecord");
		
		appendFinder(search,finderExpression);

        if(pagerModel == null){
            return queryList(finderExpression, search); 
          }
        return queryList(finderExpression, search, pagerModel);
	}

	private void appendFinder(BrokerageRecordSearch search, IFinderExpression finder) {
		if(!StringUtils.isEmpty(search.getCustomerName())){
			finder.andWhere("customer.username like #{username}")
				  .setParam("username", "%"+search.getCustomerName()+"%");
		}
		
		if(search.getStartMoney()!= null){
			finder.andWhere("brokerageRecord.brokerage >= #{startMoney}")
				  .setParam("startMoney", search.getStartMoney());
		}
		if(search.getEndMoney()!= null){
			finder.andWhere("brokerageRecord.brokerage <= #{startMoney}")
				  .setParam("endMoney", search.getEndMoney());
		}
		
		if(search.getStartDate()!=null){
			finder.andWhere("TIMESTAMPDIFF(SECOND,#{startDate},brokerageRecord.recordDate)>=0")
				  .setParam("startDate", search.getStartDate());
		}
		if(search.getEndDate()!=null){
			finder.andWhere("TIMESTAMPDIFF(SECOND,#{endDate},brokerageRecord.recordDate)<=0")
				  .setParam("endDate", search.getEndDate());
		}
		
		if(search.getUserIdentity() != null){
			finder.andWhere("customer.userIdentity=#{userIdentity}")
			  	  .setParam("userIdentity", search.getUserIdentity());
		}
		
		if(!StringUtils.isEmpty(search.getCityId())){
			finder.andWhere("customer.cityId = #{cityId}")
				  .setParam("cityId", search.getCityId());
		}
		
		if(!StringUtils.isEmpty(search.getRefereeId())){
			IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
			
			finderExpression.select("shareBrokerage.`brokerageId`")
							.from(ShareBrokerage.class,"shareBrokerage")
							.andWhere("shareBrokerage.`customerId` = #{cutomerId}");
			
			finder.andWhere("brokerageRecord.id in (" + finderExpression.toSql() + ")")
				  .setParam("cutomerId", search.getRefereeId());
		}
		
	}

	@Override
	public List<BrokerageRecord> queryList(BrokerageRecordSearch search) {
		return queryList(search,null);
	}

	@Override
	public int queryCount(BrokerageRecordSearch search) {
        IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("count(DISTINCT brokerageRecord.id) as count")
        				.from(BrokerageRecord.class,"brokerageRecord")
        				.leftJoin(Customer.class, "customer")
        				.on("customer.id","brokerageRecord.userId")
        				.setMainTableAlias("brokerageRecord");
		
		appendFinder(search,finderExpression);
		
		return queryCount(finderExpression, search);
	}
	
	@Override
	public BrokerageRecord queryModel(String key) throws DaoBaseException {
        IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
        finderExpression.select("brokerageRecord.*","customer.username as customerName","customer.userIdentity as userIdentity")
        				.from(BrokerageRecord.class,"brokerageRecord")
        				.leftJoin(Customer.class, "customer")
        				.on("customer.id","brokerageRecord.userId")
        			    .andWhere("brokerageRecord.id = #{key}")
        			    .setParam("key", key)
        				.setMainTableAlias("brokerageRecord");
		
		return queryModel(finderExpression);
	}

	@Override
	public List<BrokerageRecord> queryBrokerageRecordForStatistic(CustomerSearch search) {
		IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("brokerageRecord.*")
						.from(BrokerageRecord.class,"brokerageRecord")
						.leftJoin(Customer.class,"customer")
						.on("customer.id","brokerageRecord.userId")
						.andWhere("DATE_FORMAT( brokerageRecord.`recordDate`, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )");
		
		appendForStatistic(finderExpression,search);
		
		return queryList(finderExpression);
	}

	private void appendForStatistic(IFinderExpression finderExpression, CustomerSearch search) {
		if(!StringUtils.isEmpty(search.getCityId())){
			finderExpression.andWhere("customer.cityId = #{cityId}")
							.setParam("cityId",search.getCityId());
		}
		
	}
	
	
	
	
}